/* © 2007 Adobe Systems Incorporated. All rights reserved. */
package com.shubhjai.musiclib.data
{
	import com.shubhjai.musiclib.ApplicationModel;
	import com.shubhjai.musiclib.managers.DatabaseConnectionManager;
	import com.shubhjai.musiclib.model.Record;
	import com.shubhjai.musiclib.util.LogUtil;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.EventDispatcher;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.net.Responder;
	
	import mx.logging.ILogger;
	import mx.utils.StringUtil;

	
	
	/**
	 * This singleton class is responsible for searching the database. Its implemented as a singleton
	 * service instead of a command to keep a persistent SQLStatement and case for pending operations.
	 * 
	 * NOTE: We don't use prepared statements here due to an issue in the AIR M4 runtime beta with quickly
	 * reused prepared SQLStatements. 
	 */ 
	public class SearchService extends EventDispatcher
	{
		
		private static var instance : SearchService;
		
		private var conn : SQLConnection;
		private var inited : Boolean;

		private var pendingTerm : String;
		
		
		private var empSearchStmt1 : SQLStatement;
		private var empSearchStmt2 : SQLStatement;
		
		private var model : ApplicationModel;
		
		private var searchTerm1 : String;
		private var searchTerm2 : String;
	
		
		
		protected var logger : ILogger;
		
		/**
		 * Private constructor. Use getInstance() instead.
		 */
		public function SearchService()
		{
			if ( instance != null )
			{
				throw new Error("Private constructor. Use getIntance() instead.");	
			}
			model = ApplicationModel.getInstance();	
			
			
			// the onConnOpen will only get invoked if the service gets the first connection. This should possibly be refactored,
			// especially as there is only one SQL connection.
			conn = DatabaseConnectionManager.getInstance().getConnection( onConnOpen );
			
			empSearchStmt1 = new SQLStatement();
			empSearchStmt1.sqlConnection = conn;
			empSearchStmt1.itemClass = Record;
			
			// ideally, these would be prepared statements
			//empSearchStmt1.text = "SELECT * from employee where firstName like :firstName or " + 
 			//				"lastName like :lastName or id like :id ORDER BY lastName;"
 			//empSearchStmt1.prepare();
 							
			empSearchStmt2 = new SQLStatement();
			empSearchStmt2.sqlConnection = conn;
			empSearchStmt2.itemClass = Record;

			
			logger = LogUtil.getLogger( this );
		}
		
		
		
		/**
		 * Get an instance of the DataManager.
		 */ 
		public static function getInstance() : SearchService
		{
			if ( instance == null )
			{
				instance = new SearchService();
			}
			return instance;
		}
	
 		
 		/**
 		 * Search for employees and conference rooms based on the current terms. 
 		 * 
 		 */ 
 		public function search( term:String ) : void
 		{
 			logDebug("search: [" + term + "] connected: " + conn.connected + " : executing: " + isSearchExecuting());
 			if ( ! conn.connected || isSearchExecuting() )
 			{
 				pendingTerm = term;
 			}
 			else
 			{
 				
 				if ( term.length == 0 )
 				{
 					model.updateSearchResults( null );	
		          	return;
 				}
 				
	 			var parts : Array = StringUtil.trim( term ).split(' ');
 				
 				searchTerm1 = searchTerm2 = null;
 				if ( parts.length == 1 )
 				{
 					searchTerm1 = parts[0] + "%";
 					
 					empSearchStmt1.text = "SELECT * from employee where firstName like '" + searchTerm1 + "' or " + 
 							"lastName like '" + searchTerm1 + "' or id like '" + searchTerm1 + "' ORDER BY lastName;" ;
 					
 					//logDebug("empSearch1.text: " + empSearchStmt1.text);
 					try 
	 				{
		 				empSearchStmt1.execute( 500, new Responder( onEmpSearchResult, onSearchError ) );	
	 				}
	 				catch ( e:Error )
	 				{
	 					trace( "error executing search: " + e.message );
	 				}
 					
 				}
 				else if ( parts.length > 1 )
 				{
 					searchTerm1 = parts.shift() + "%";
 					searchTerm2 = parts.join(" ") + "%";
 					empSearchStmt2.text = "SELECT * from employee where firstName like '" + searchTerm1 + "' and " + 
 							"lastName like '" + searchTerm2 + "' ORDER BY lastName;";

 					try 
	 				{
	 					//logDebug("empSearch2.text: " + empSearchStmt1.text);
		 				empSearchStmt2.execute( 500, new Responder( onEmpSearchResult, onSearchError ) );	
	 				}
	 				catch ( e:Error )
	 				{
	 					trace( "error executing search: " + e.message );
	 				}
 					
 				}
 				
 				
 				
 			}
 		}
 		
		private function onEmpSearchResult( res:SQLResult ) : void
		{
			logDebug("onEmpSearchResult(): " + res.complete);
			
			// TRICKY: This is so that an identical search doesn't paginate the results. 
			if ( ! res.complete && empSearchStmt1.getResult() == res )
				empSearchStmt1.reset();
				
			if ( ! res.complete && empSearchStmt2.getResult() == res )
				empSearchStmt2.reset();

			if ( res.data == null )
			{
				model.updateSearchResults( null );
			}
			else
			{
				
				model.updateSearchResults( res.data.slice() );
			}
		}
		
		private function onSearchError ( err:SQLError ) : void
		{
			logDebug("onSearchError(): " + err);
			
			// ignore these errors - there may be sporadic DB errors in Apollo beta. Not app-critical.
			
			//var evt : ErrorEvent = new ErrorEvent( ErrorEvent.ERROR );
			//evt.text = "onSearchError: " + err.toString();
			//dispatchEvent( evt );
		}
		
		
		private function onConnOpen( evt:SQLEvent ) : void
		{
			logDebug("onConnOpen()");
			if ( pendingTerm )
			{
				var tmp : String = pendingTerm;
				pendingTerm = null;
				search( tmp );
			}
		}
		
		
		private function isSearchExecuting() : Boolean
		{
			return empSearchStmt1.executing || empSearchStmt2.executing;
		}
		
		private function logDebug( msg:String ) : void
		{
			logger.debug( ": " + msg );	
		}
		
	}
}